![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
How the Oracle Index WorksWhen an index is created, an index segment is automatically allocated. This index segment contains information that speeds access to data by determining the location of indexed data with as few I/Os as possible. Oracle indexes data by using an index structure known as a B*-Tree index. A B*-Tree index is designed to balance the access time to any row. A B*-Tree index is a tree of descending comparison values (see Figure 10.6). As you traverse down the index, you compare the desired value with the values in the upper-level index blocks called branch blocks. Based on the outcome of the comparison with the branch blocks, you compare the desired value with more branch blocks until you reach the lowest-level index blocks. The index blocks on the lowest level, called leaf blocks, contain every indexed data value and the associated ROWID of that data.
With a unique index, there is one ROWID per data value in the leaf block (see Figure 10.7). With a nonunique index, there may be several values associated with the data value. In the case of the nonunique index, the data values are sorted first by the index key and then by the ROWID.
With a B*-Tree index, all the leaf blocks are at the same level. Access of index data takes approximately the same time regardless of the value of the data. B*-Tree indexes provide quick access to data whether it is an exact match or a range query. In addition, B*-Tree indexes provides good performance regardless of the size of the tableand the performance does not degrade as the table grows. What To IndexAn index is effective only when it is used. The use of the index is mostly determined by the column values that are indexed. Remember that the more indexes you have on a table, the more overhead is incurred during updates, inserts, and deletes. Therefore, it is important to index selectively. Use the following guidelines for deciding which tables to index:
If you decide to use an index, it is important to decide the columns on which you put the index. Depending on the table, you may choose to index one or more columns. Use the following guidelines for deciding which columns to index:
In certain situations, the use of composite indexes may be more effective than individual indexes. Here are some examples of where composite indexes may be quite useful:
Composite indexes can be quite useful when they are carefully designed. As with single-column indexes, they are most effective if applications are written with the indexes in mind. Once you have created the index, you should periodically use the SQL Trace facility to determine whether your queries are taking advantage of the indexes. It may be worth the effort to try the query with and without indexes and then compare the results to see whether the index is worth the space it uses. In summary, indexes can significantly improve performance in your system if they are used properly. You must first decide whether an index is appropriate for the data and access patterns in your particular system. Once you decide to use an index, you must decide which columns to index. Indexing an inappropriate column or table can actually reduce performance. Indexing appropriately can greatly improve performance by reducing I/Os and speeding access times. Careful planning and periodic testing with the SQL Trace feature can lead to a very effective use of indexes, with optimal performance being the outcome.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |